Hash-join in parallel computation environments

ABSTRACT

According to some embodiments, a system and method for a parallel join of relational data tables may be provided by calculating, by a plurality of concurrently executing execution threads, hash values for join columns of a first input table and a second input table; storing the calculated hash values in a set of disjoint thread-local hash maps for each of the first input table and the second input table; merging the set of thread-local hash maps of the first input table, by a second plurality of execution threads operating concurrently, to produce a set of merged hash maps; comparing each entry of the merged hash maps to each entry of the set of thread-local hash maps for the second input table to determine whether there is a match, according to a join type; and generating an output table including matches as determined by the comparing.

FIELD

Some embodiments relate to a data structure. More specifically, someembodiments provide a method and system for a data structure and use ofsame in providing a relational data join operation in parallel computingenvironments.

BACKGROUND

A number of presently developed and developing computer systems includemultiple processors in an attempt to provide increased computingperformance. Advances in computing performance, including for exampleprocessing speed and throughput, may be provided by parallel computingsystems and devices as compared to single processing systems thatsequentially process programs and instructions.

For parallel join processes, a number of approaches have been proposed.However, the previous approaches each include sequential operationsand/or synchronization operations such as, locking, to avoidinconsistencies or lapses in data coherency. Thus, prior proposedsolutions for parallel join operations and processes in parallelcomputation environments with shared memory either contain a sequentialstep(s) and/or require some sort of synchronization on the datastructures.

Accordingly, a method and mechanism for efficiently processing joinprocesses in parallel computation environments are provided by someembodiments herein.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is block diagram of a system according to some embodiments;

FIG. 2 is a block diagram of an operating environment according to someembodiments;

FIGS. 3A-3C are illustrative depictions of various aspects of a datastructure according to some embodiments;

FIG. 4 is a flow diagram of a method relating to a data structure,according to some embodiments herein;

FIG. 5 is an illustrative depiction of a parallel join flow, in someembodiments herein;

FIGS. 6A and 6B provide illustrative examples of data structures, inaccordance with some embodiments herein; and

FIGS. 7-9 relate to threading operations, according to some embodimentsherein.

DETAILED DESCRIPTION

In an effort to more fully and efficiently use the resources of aparticular computing environment, a data structure and techniques ofusing that data structure may be developed to fully capitalize on thedesign characteristics and capabilities of that particular computingenvironment. In some embodiments herein, a data structure and techniquesfor using that data structure (i.e., algorithms) are provided forefficiently using the data structure disclosed herein in a parallelcomputing environment with shared memory.

As used herein, the term parallel computation environment with sharedmemory refers to a system or device having more than one processingunit. The multiple processing units may be processors, processor cores,multi-core processors, etc. All of the processing units can access amain memory (i.e., a shared memory architecture). All of the processingunits can run or execute the same program(s). As used herein, a runningprogram may be referred to as a thread. Memory may be organized in ahierarchy of multiple levels, where faster but smaller memory units arelocated closer to the processing units. The smaller and faster memoryunits located nearer the processing units as compared to the main memoryare referred to as cache.

FIG. 1 is a block diagram overview of a device, system, or apparatus 100that may be used in a providing an index hash table or hash map inaccordance with some aspects and embodiments herein, as well asproviding a parallel join process based on such data structures. System100 may be, for example, associated with any of the devices describedherein and may include a plurality of processing units 105, 110, and115. The processing units may comprise one or more commerciallyavailable Central Processing Units (CPUs) in form of one-chipmicroprocessors or a multi-core processor, coupled to a communicationdevice 120 configured to communicate via a communication network (notshown in FIG. 1) to a end client (not shown in FIG. 1). Device 100 mayalso include a local cache memory associated with each of the processingunits 105, 110, and 115 such as RAM memory modules. Communication device120 may be used to communicate, for example, with one or more clientdevices or business service providers. System 100 further includes aninput device 125 (e.g., a mouse and/or keyboard to enter content) and anoutput device 130 (e.g., a computer monitor to display a user interfaceelement).

Processing units 105, 110, and 115 communicates with a shared memory 135via a system bus 175. System bus 175 also provides a mechanism for theprocessing units to communicate with a storage device 140. Storagedevice 140 may include any appropriate information storage device,including combinations of magnetic storage devices (e.g., a hard diskdrive), optical storage devices, and/or semiconductor memory devices forstoring data and programs.

Storage device 140 stores a program 145 for controlling the processingunits 105, 110, and 115 and query engine application 150 for executingqueries. Processing units 105, 110, and 115 may perform instructions ofthe program 145 and thereby operate in accordance with any of theembodiments described herein. For example, the processing units mayconcurrently execute a plurality of execution threads to build the indexhash table data structures disclosed herein. Furthermore, query engine150 may operate to execute a parallel join operation in accordance withaspects herein in cooperation with the processing units and by accessingdatabase 155. Program 145 and other instructions may be stored in acompressed, uncompiled and/or encrypted format. Program 145 may alsoinclude other program elements, such as an operating system, a databasemanagement system, and/or device drivers used by the processing units105, 110, and 115 to interface with peripheral devices.

In some embodiments, storage device 140 includes a database 155 tofacilitate the execution of queries based on input table data. Thedatabase may include relational data tables, data structures (e.g.,index hash tables), rules, and conditions for executing a query in aparallel computation environment such as that of FIGS. 1 and 2.

In some embodiments, the data structure(s) disclosed herein as beingdeveloped for use in parallel computing environments with shared memoryis referred to as a parallel hash table. In some instances, the parallelhash table may also be referred to as a parallel hash map. In general, ahash table may be provided and used as index structures for data storageto enable fast data retrieval. The parallel hash table disclosed hereinmay be used in a parallel computation environment where multipleconcurrently executing (i.e., running) threads insert and retrieve datain tables. Furthermore, a hash-join algorithm that uses the parallelhash tables herein is provided for computing a join in a parallelcomputation environment.

FIG. 2 provides an illustrative example of a computation environment 200compatible with some embodiments herein. While computation environment200 may be compatible with some embodiments of the data structures andthe methods herein, the data structures and the methods herein are notlimited to the example computation environment 200. Processes to store,retrieve, and perform operations on data may be facilitated by adatabase system (DBS) and a database warehouse (DWH).

As shown in FIG. 2, DBS 210 is a server. DBS 210 further includes adatabase management system (DBMS) 215. DBMS 215 may comprise software(e.g., programs, instructions, code, applications, services, etc.) thatcontrols the organization of and access to database 225 that storesdata. Database 225 may include an internal memory, an external memory,or other configurations of memory. Database 225 may be capable ofstoring large amounts of data, including relational data. The relationaldata may be stored in tables. In some embodiments, a plurality ofclients, such as example client 205, may communicate with DBS 210 via acommunication link (e.g., a network) and specified applicationprogramming interfaces (APIs). In some embodiments, the API languageprovided by DBS 210 is SQL, the Structured Query Language. Client 205may communicate with DBS 115 using SQL to, for example, create anddelete tables; insert, update, and delete data; and query data.

In general, a user may submit a query from client 205 in the form of aSQL query statement to DBS 210. DBMS 215 may execute the query byevaluating the parameters of the query statement and accessing database225 as needed to produce a result 230. The result 230 may be provided toclient 205 for storage and/or presentation to the user.

One type of query is a join query. The join query may operate to combinefields from two tables by using values common to each table. As will beexplained in greater detail below, a parallel join algorithm, process,or operation may be used to compute SQL joins. In general with referenceto FIG. 2, some embodiments herein may include client 205 wanting tojoin the data of two tables stored in database 225 (e.g., a user atclient 205 may desire to know all customers who bought a certainproduct). Client 205 may connect to DBS 210 and issue a SQL querystatement that describes the join. DBMS 215 may create a executableinstance of the parallel join algorithm herein, provide it with theinformation needed to run the parallel join algorithm (e.g., the name oftables to access, columns to join, etc.), and run the parallel joinoperation or algorithm. In the process of running, the parallel joinalgorithm herein may create an index hash map 220 to keep track ofintermediate result data. An overall result comprising a result tablemay be computed based on the index hash map(s) containing theintermediate results. The overall parallel join result may betransmitted to client 205.

As an extension of FIG. 2, DWHs may be built on top of DBSs. Thus, ause-case of a DWH may be similar in some respects to DBS 210 of FIG. 2.

The computation environment of FIG. 2 may include a plurality ofprocessors that can operate concurrently, in parallel and include adevice or system similar to that described in FIG. 1. Additionally, thecomputation environment of FIG. 2 may have a memory that is sharedamongst the plurality of processors, for example, like the system ofFIG. 1. In order to fully capitalize on the parallel processing power ofsuch a computation environment, the data structures used by the systemmay be designed, developed or adapted for being efficiently used in theparallel computing environment.

A hash table is a fundamental data structure in computer science that isused for mapping “keys” (e.g., the names of people) to the associatedvalues of the keys (e.g., the phone number of the people) for fast datalook-up. A conventional hash table stores key-value pairs. Conventionalhash tables are designed for sequential processing.

However, for parallel computation environments there exists a need fordata structures particularly suitable for use in the parallel computingenvironment. In some embodiments herein, the data structure of an indexhash map is provided. In some aspects, the index hash map provides alock-free cache-efficient hash data structure developed to parallelcomputation environments with shared memory. In some embodiments, theindex hash map may be adapted to column stores.

In a departure from conventional hash tables that store key-value pairs,the index hash map herein does not store key-value pairs. The index hashmap herein generates key-index pairs by mapping each distinct key to aunique integer. In some embodiments, each time a new distinct key isinserted in the index hash map, the index hash map increments aninternal counter and assigns the value of the counter to the key toproduce a key-index pair. The counter may provide, at any time, thecardinality of an input set of keys that have thus far been inserted inthe hash map. In some respects, the key-index mapping may be used toshare a single hash map among different columns (or value arrays). Forexample, for processing a plurality of values distributed amongdifferent columns, the associated index for the key has to be calculatedjust once. The use of key-index pairs may facilitate bulk insertion incolumnar storages. Inserting a set of key-index pairs may entailinserting the keys in a hash map to obtain a mapping vector containingindexes. This mapping vector may be used to build a value array pervalue column.

Referring to FIGS. 3A-3C, input data is illustrated in FIG. 3A includinga key array 305. For each distinct key 315 from key array 305, the indexhash map returns an index 320 (i.e., a unique integer), as seen in FIG.3B. When all of the keys, from a column for example, have been insertedin the hash map, the mapping vector 325 of FIG. 3C results. To achieve amaximum parallel processor utilization, the index hash maps herein maybe designed to avoid locking when being operated on by concurrentlyexecuting threads by producing wide data independence. In someembodiments, index hash maps herein may be described by a frameworkdefining a two step process. In a first step, input data is split orseparated into equal-sized blocks and the blocks are assigned to workerexecution threads. These worker execution threads may produceintermediate results by building relatively small local hash tables orhash maps. The local hash maps are private to the respective thread thatproduces it. Accordingly, other threads may not see or access the localhash map produced by a given thread.

In a second step, the local hash maps including the intermediate resultsmay be merged to obtain a global result by concurrently executing mergerthreads. When accessing and processing the local hash maps, each of themerger threads may only consider a dedicated range of hash values. Themerger threads may process hash-disjoint partitions of the local hashmaps and produce disjoint result hash tables that may be concatenated tobuild an overall result.

FIG. 4 is a flow diagram related to a data structure framework 400, inaccordance with some embodiments herein. At S405, an input data table isseparated or divided into a plurality of partitions. The size of thepartitions may relate to or even be the size of a memory unit such as,for example, a cache associated with parallel processing units. In someembodiments, the partitions are equal in size. Furthermore, a firstplurality of execution threads running in parallel may each generate alocal hash table or hash map. Each of the local hash maps is private tothe one of the plurality of threads that generated the local hash map.

The second step of the data structure framework herein is depicted inFIG. 4 at S410. At S410, the local hash maps are merged. The merging ofthe local hash maps produces a set of disjoint result hash tables orhash maps.

In some embodiments, when accessing and processing the local hash maps,each of the merger threads may only consider a dedicated range of hashvalues. From a logical perspective, the local hash maps may beconsidered as being partitioned by their hash value. One implementationmay use, for example, some first bits of the hash value to form a rangeof hash values. The same ranges are used for all local hash maps, thusthe “partitions” of the local hash maps are disjunctive. As an example,if a value “a” is in range 5 of a local hash map, then the value will bein the same range of other local hash maps. In this manner, allidentical values of all local hash maps may be merged into a singleresult hash map. Since the “partitions” are disjunctive, the mergedresult hash maps may be created without a need for locks. Additionally,further processing on the merged result hash maps may be performedwithout locks since any execution threads will be operating ondisjunctive data.

In some embodiments, the local (index) hash maps providing theintermediate results may be of a fixed size. Instead of resizing a localhash map, the corresponding worker execution thread may replace itslocal hash map with a new hash map when a certain load factor is reachedand place the current local hash map into a buffer containing hash mapsthat are ready to be merged. In some embodiments, the size of the localhash maps may be sized such that the local hash maps fit in a cache(e.g., L2 or L3). The specific size of the cache may depend on the sizesof caches in a given CPU architecture.

In some aspects, insertions and lookups of keys may largely take placein cache. In some embodiments, over-crowded areas within a local hashmap may be avoided by maintaining statistical data regarding the localhash maps. The statistical data may indicate when the local hash mapshould be declared full (independent of an actual load factor). In someaspects and embodiments, the size of a buffer of a computing system andenvironment holding local hash maps ready to be merged is a tuningparameter, wherein a smaller buffer may induce more merge operationswhile a larger buffer will necessarily require more memory.

In some embodiments, a global result may be organized into bucketedindex hash maps where each result hash map includes multiple fixed-sizephysical memory blocks. In this configuration, cache-efficient mergingmay be realized, as well as memory allocation being more efficient andsustainable since allocated blocks may be shared between queries. Insome aspects, when a certain load factor within a global result hash mapis reached during a merge operation, the hash map may be resized.Resizing a hash map may be accomplished by increasing its number ofmemory blocks. Resizing of a bucketed index hash map may entailrepositioning the entries of the hash map. In some embodiments, themaps' hash function may be chosen such that its codomain increases byadding further least significant bits if needed during a resizeoperation. In an effort to avoid too many resize operations, an estimateof a final target size of the map may be determined before an actualresizing of the hash map.

In some embodiments, the index hash map framework discussed above mayprovide an infrastructure to implement parallelized query processingalgorithms or operations. One embodiment of a parallelized queryprocessing algorithm includes a hash-based (equi-)join, as will bediscussed in greater detail below.

In some embodiments, a join algorithm herein is hash-based. Thishash-based join algorithm may be used to combine two input tables. Inaccordance with some aspects and embodiments, the input tables arehashed by multiple execution threads using the index hash tableframework described hereinabove. Since the result tables of the indexhash tables are disjoint, all subsequent processing steps performed onthe disjoint result tables can be executed in parallel by one thread perpartition without a need to use locks.

In some embodiments, the resulting table may not be constructed bycopying all values to their final positions in the columns. Instead, theresulting table may be a virtual table. The virtual table may holdreferences to the original columns and have a vector of all rows thatmatch each other, according to the join type being performed. Uponaccess to a row, a call to do so may be routed transparently to therespective row of the original column. A benefit of the virtual resultis that it is not necessary to copy the data.

In some embodiments, the hash-based join algorithm and methods hereinuse a data allocation and organization method that does not need to knowthe number of distinct values in advance.

In an effort to fully utilize the resources of parallel computingenvironments with shared memory, a join operation should be computed anddetermined in parallel. In an instance the join is not computed inparallel, the processing performance for the join would be bound by thespeed of a single processing unit instead of being realized by themultiple processing units available in the parallel computingenvironment.

FIG. 5 is an illustrative depiction of a parallel hash-based join flow,according to some embodiments herein. In some aspects, the parallel joinflow 500 uses the index hash table framework discussed hereinabove. Inthe example of FIG. 5, two degrees of parallelism are depicted and areachieved by the concurrent execution of two execution threads 510 and515. However, the concepts conveyed by FIG. 5 may be extended toadditional degrees of parallelism, including computation environmentsnow known and those that become known in the future.

In FIG. 5, two input tables are hashed using the index hash tableframework. For purposes of clarity, only one of the two input tables isdepicted in FIG. 5 since the hashing of the input tables is the same foreach input table. As an initial step, multiple concurrently runningexecution threads calculate hash values for the join columns of bothtables. The join columns are the columns specified in a join statement(e.g., a SQL join query statement). These hash values are inserted intothread-local hash maps, in accordance with the index hash tableframework discussed above. One set of thread-local hash maps areproduced for the smaller table 505 and one, 560, for the bigger table(not shown). As illustrated in FIG. 5, the input table is divided intopartitions, such as, partitions 520 and 525, and a plurality ofexecution threads 510, 515 operate to produce disjoint local thread hashmaps 530, 535. The hash map for the second, bigger input table isprovided at 560 and is similar to the hash maps 530, 535. In addition toproviding key-index pairs, the thread local hash maps also include therow number or row identifier of the original column corresponding to thevalue referenced by each hash map entry.

Proceeding with the flow of the join operation in FIG. 5, the threadlocal hash maps 530, 535 of the smaller table are merged into one hashmap per partition. This aspect may be accomplished in some embodimentsby one thread per core operating to merge all partitions that belong toeach other into a single hash map. Merged hash tables for the smallerinput table 505 are depicted at 545, 550, and 560. The merging of thethread local hash maps of the smaller table may be accomplished by aplurality of execution threads operating in parallel.

In the example of FIG. 5, the set of hash maps 560 of the bigger inputtable are not merged. Each entry of the bigger table hash maps areprobed against or compared to the merged hash maps of the smaller table.If a match is found, then both tables have rows to be joined with eachother.

However, while there is a match between the hash maps, the matched rowseach have a different identifier. Therefore, the matched rows arealigned or otherwise reconciled so that all corresponding rows of bothtables can be retrieved by using a single identifier. In an instance avalue exists only in one of the two tables, then it is kept only if oneof the outer join types (e.g., left outer, right outer, full outer) arebeing performed.

Based on the row identifiers determined for the matches, an output table570 may be generated. For example, all matching rows are added to theoutput table 570. In an instance of outer joins, the lines withoutmatches but satisfying the outer join operation are added to the outputtable 570 as well, with the addition of a NULL indicator.

In some embodiments, the hash maps of the bigger (or second) input tablemay be merged instead of the smaller (first) input table or in additionto the merging of the smaller (first) input table. Turning to adiscussion of the data structures used in some embodiments herein, it isagain noted that each entry in a hash map refers to the row in theoriginal columns where the corresponding value is stored (i.e., the rownumber is stored with the hash map entries). However, the number ofdistinct values in a column is unknown. Therefore, it is not possible topre-allocate a “reasonable” amount of memory in advance. Moreover, hashmaps are merged and therefore the rows to which entries in the hash mapsrefer to have to be merged as well if two hash maps store the samevalue. A data structure 600 capable of accommodating these concerns isillustrated in FIGS. 6A and 6B.

The data structure of FIG. 6A includes an array 610 as well as head andtail records 615 and 620 which are accessed through the index given bythe hash map framework. FIG. 6A shows a line through input table 605indicating that the input table is partitioned and each part isprocessed by one of the execution threads 625 and 630. Each executionthread processes a (not necessarily consecutive) portion of input table605 and fills the corresponding part of array 610.

For each input table 605, an integer array 610 is allocated. Each fieldnumber of array 610 corresponds to a row in input table 605. For eachentry in a hash map, head and tail values are stored that describestarting and end points in that array. As an example, if the singlevalue “a” is in row 5 of an input table, thread 625 produces a stopindicator stored at field 5 of the array. The position “5” is stored ashead and tail values for the hash map value “a”. When all positions ofvalue “5” are queried, the tail is used to access the array. Position 5is the first matching row of the input table. Since the position 5contains only the stop indicator, it is known that only row 5 has thevalue “a”.

In the instance more than one row of an input table has a certain value,each field in the array having the particular value will store the rownumber of the next field containing that value. For example, rows 10 and15 operated on by thread 630 store the value “a” in the input table 605.In data structure 620, “15” will be stored as the tail value and “10” isstored as the head value. In the array 610, field 15 will store “10” andfield 10 will store a stop indicator. To retrieve all rows where value“a” is stored, rows 15 and 10 can be retrieved by following thereferences in the array 610. FIG. 6A provides an illustrative depictionof an example of two data structures 615 and 620 that store head andtail values. When two hash maps are merged and both store the samevalue, the sequences in the array have to be merged as well. FIG. 6Bprovides an illustrative depiction of a merged array 635 according tosome embodiments herein, where the two sequences produced by the twothreads 625 and 630 have been merged. The merging of the two datastructures 615 and 620 results in the combined data structure 640 withone head value and one tail value. Array 635 reflects the merging. Thehead and tail values for the merged data structures are updated so thatthey point to the new head and tail of the combined sequences.

As described above, including the discussion of FIG. 5, the hash-basedparallel join algorithm herein includes the phases of (1) hashing thesmaller table, (2) hashing the bigger table, (3) merging the hash mapsof the smaller table, (4) probing the entries of hash maps for thebigger table against the merged hash maps, and (5) building the outputtable.

In some embodiments, the two input tables may be hashed in any order.Therefore, (worker/hasher) threads can process parts of the input tablesin any logical order to create the hash maps for the input tables. Insome embodiments, the hash maps of the bigger table may be created aftera process of merging the hash maps for the smaller table. FIG. 7provides an illustrative depiction of a threading scenario 700. In thisexample, parts of the smaller input table are hashed at 705, parts ofthe larger input table are hashed at 710, and then the hash maps of thesmaller input table are merged at 715. Thereafter, the merged hash mapsof the smaller input table and the hash maps of the bigger input tableare probed at 720, followed by the building of the output table at 725.

In order to hash the two input tables, execution threads pick up parts(e.g., chunks) of data and create hash maps. This results in twoseparate sets of hash maps, one set for each input table. Each hash mapis then partitioned. In some embodiments, as many parts are created ascores are available in a parallel computation system (e.g., FIG. 1,system 100). After the partitioning of the hash tables, all subsequentsteps are then executed for all parts that belong to each other.

In some aspects, a thread for a specific part considers all parts of allinput hash maps, merges the parts of the required hash maps, probes thevalues and builds the output table. As an example, consider a systemwith 32 processing cores. In this example, 32 threads may be started tohash the input tables. When all input tables are hashed, the thread for,as an example, part 4 considers all hash maps of the smaller inputtables and merges their part 4 into a new hash map which is thendedicated for all values for part 4. Thread 4 then considers part 4 ofeach hash map of the bigger table. The part 4 portions of the biggerinput table are then probed against the part 4 portions of the mergedhash map. Then, the thread builds the output of part 4.

In some embodiments, each part (i.e., partitioned portion) hasapproximately the same size since the hash function may equallydistribute values. However, the sizes may still differ based on theinput data.

The process steps of merging, probing and building the output table areclosely coupled since each process step cannot start before a previousstep has finished. Therefore, it may be advantageous to start directlymerging the smaller table hash maps when the hash maps are built. If onepart being hashed is smaller than the other parts being hashed inparallel, the responsible thread may use the time freed by processing ofthe smaller part of the smaller table to hash the bigger table. Thisaspect is illustrated in FIG. 8 where threading scenario 800 illustratesthe merging process step 810 is executed after the hashing of thesmaller table at 805 and before the bigger table is hashed at 815. Whenall parts are merged at 810, the threads each get part of the biggertable and start hashing them at 815 until no portions of the biggertable remain un-hashed. The threads then proceed with the probing phaseat 820.

In both FIGS. 7 and 8, thread 3 has the longest runtime. The processingfinishes when all threads are done. The total runtime in FIG. 8 isshorter than in FIG. 7. Thus, modifying the order of executing thephases may have a positive impact on the overall runtime if the extratime for a bigger part is longer than the time that is required to hasha portion of the input table.

The earliest time permissible to start the merging of the smaller tablehash tables is right after all of the hash maps for the smaller tableare created. This is illustrated in FIG. 8 at 830 that includes a whitegap (i.e., no processing activity) for thread 2. In such a case, the gapin time in processing of the smaller table may be used for hashing achunk of the bigger table. Since merging should start as early aspossible, the chunk size is reduced in this case.

The threading task distribution may be further optimized. For example,the size of a part may be determined during the merge phase. If the partof the smaller table is bigger due to the distribution of values by thehash function, it is likely that the parts of the bigger table will alsobe bigger. However, even if this is not the case, the probing and outputphase may take longer due to the bigger merged tables. Therefore, theamount of chunks or portions of the bigger table processed by the threadthat handles the bigger parts can be reduced.

FIG. 9 includes a number of optimizations, including hashing and mergingthe smaller table before hashing of the bigger table and processing aportion of the bigger table by a thread during a period of otherwiseinactivity. As shown, the total runtime is shorter as the thread thattakes the most time, thread 3, does not hash any chunks of the biggertable under the optimization scenario 900 of FIG. 9.

Each system described herein may be implemented by any number of devicesin communication via any number of other public and/or private networks.Two or more of the devices herein may be co-located, may be a singledevice, or may be located remote from one another and may communicatewith one another via any known manner of network(s) and/or a dedicatedconnection. Moreover, each device may comprise any number of hardwareand/or software elements suitable to provide the functions describedherein as well as any other functions. Other topologies may be used inconjunction with other embodiments.

All systems and processes discussed herein may be embodied in programcode stored on one or more computer-readable media. Such media mayinclude, for example, a floppy disk, a CD-ROM, a DVD-ROM, magnetic tape,and solid state Random Access Memory (RAM) or Read Only Memory (ROM)storage units. According to some embodiments, a memory storage unit maybe associated with access patterns and may be independent from thedevice (e.g., magnetic, optoelectronic, semiconductor/solid-state, etc.)Moreover, in-memory technologies may be used such that databases, etc.may be completely operated in RAM memory at a processor. Embodiments aretherefore not limited to any specific combination of hardware andsoftware.

Embodiments have been described herein solely for the purpose ofillustration. Persons skilled in the art will recognize from thisdescription that embodiments are not limited to those described, but maybe practiced with modifications and alterations limited only by thespirit and scope of the appended claims.

1-20. (canceled)
 21. A computer readable medium having programinstructions stored thereon, the medium comprising: instructions tocalculate, by a plurality of concurrently executing execution threads,hash values for join columns of a first input table and a second inputtable, the hash values including key-index pairs where each key of thekey-index pairs that is distinct is mapped to a unique integer, the keyof the key-index pairs being extracted from partitions of the first andsecond input tables, and the index of the key-index pairs comprising oneof the unique integers; instructions to store the calculated hash valuesin a set of disjoint thread-local hash maps for each of the first inputtable and the second input table, the storing including storing a rownumber of the join columns of the first or second input tablecorresponding to an associated value stored for each of the key-indexpairs; instructions to merge the set of thread-local hash maps of thefirst input table, by a second plurality of execution threads operatingconcurrently, to produce a set of merged hash maps, each of the secondplurality of execution threads responsible for a dedicated range of allof the thread-local hash maps; instructions to compare each entry of themerged hash maps to each entry of the set of thread-local hash maps forthe second input table to determine whether there is a match, accordingto a join type; and instructions to generate an output table includingmatches as determined by the comparing.
 22. The medium of claim 21,wherein the join columns and the join type are specified by a join querystatement.
 23. The medium of claim 21, wherein the join type may be oneof an inner join, an outer join, a left-outer join, and a right-outerjoin.
 24. The medium of claim 21, wherein the first input table issmaller than the second input table.
 25. The medium of claim 21, whereinthe second input table is smaller than the first input table.
 26. Themedium of claim 21, further comprising instructions to separate thefirst input table and the second input table into a plurality of thepartitions.
 27. The medium of claim 21, further comprising instructionsto assign the partitions to the execution threads.
 28. The medium ofclaim 21, further comprising program instructions to align the rownumber of the matches to facilitate retrieval of all corresponding rowsfrom both the first input table and the second input table.
 29. Themedium of claim 21, wherein the set of thread-local hash maps of thesecond input table is merged instead of or in addition to the merging ofthe set of the thread-local hash maps of the first input table.
 30. Themedium of claim 21, wherein the calculating and storing of hash valuesand the merging is completed before a start of the comparing.
 31. Themedium of claim 21, further comprising program instructions to partitioneach of the thread-local hash maps to facilitate a determination of thededicated range of all of the thread-local hash maps each of the secondplurality of execution threads is responsible.
 32. A computerimplemented method, the method comprising: separating a first inputtable and a second input table into a plurality of the partitionscalculating, by a plurality of concurrently executing execution threads,hash values for join columns of the first input table and the secondinput table, the hash values including key-index pairs where each key ofthe key-index pairs that is distinct is mapped to a unique integer, thekey of the key-index pairs being extracted from partitions of the firstand second input tables, and the index of the key-index pairs comprisingone of the unique integers; storing the calculated hash values in a setof disjoint thread-local hash maps for each of the first input table andthe second input table, the storing including storing a row number ofthe join columns of the first or second input table corresponding to anassociated value stored for each of the key-index pairs; merging the setof thread-local hash maps of the first input table, by a secondplurality of execution threads operating concurrently, to produce a setof merged hash maps, each of the second plurality of execution threadsresponsible for a dedicated range of all of the thread-local hash maps;comparing each entry of the merged hash maps to each entry of the set ofthread-local hash maps for the second input table to determine whetherthere is a match, according to a join type; and generating an outputtable including matches as determined by the comparing.
 33. The methodof claim 32, wherein the join columns and the join type are specified bya join query statement.
 34. The method of claim 32, wherein the jointype may be one of an inner join, an outer join, a left-outer join, anda right-outer join.
 35. The method of claim 32, wherein the second inputtable is smaller than the first input table.
 36. The method of claim 32,further comprising instructions to assign the partitions to theexecution threads.
 37. The method of claim 32, further comprisingprogram instructions to align the row number of the matches tofacilitate retrieval of all corresponding rows from both the first inputtable and the second input table.
 38. The method of claim 32, whereinthe set of thread-local hash maps of the second input table is mergedinstead of or in addition to the merging of the set of the thread-localhash maps of the first input table.
 39. The method of claim 32, whereinthe calculating and storing of hash values and the merging is completedbefore a start of the comparing.
 40. The method of claim 32, furthercomprising program instructions to partition each of the thread-localhash maps to facilitate a determination of the dedicated range of all ofthe thread-local hash maps each of the second plurality of executionthreads is responsible.